package net.khoroshev.wschat.dao;

import net.khoroshev.wschat.model.entity.AuthUser;
import net.khoroshev.wschat.model.entity.UserAuthority;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.util.Assert;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * User: vfirstov
 * Date: 01.12.13
 * Time: 23:49
 *
 */
public class JBDCProvider extends JdbcDaoSupport implements DataStorageProvider {

    private static final String CREATE_USER_SQL =
            "INSERT INTO USERS (USERNAME, PASSWORD, ENABLED, FIRSTNAME, LASTNAME) VALUES (?,?,?,?,?)";

    private static final String CREATE_AUTHORITY_SQL =
            "INSERT INTO AUTHORITIES(USER_ID, AUTHORITY) " +
                    "SELECT USER_ID, ? " +
                    "FROM USERS " +
                    "WHERE USERNAME = ? ";

    private static final String SELECT_USER_SQL =
            "SELECT USER_ID, USERNAME, PASSWORD, ENABLED, FIRSTNAME, LASTNAME " +
            "FROM USERS WHERE USERNAME = ?";

    private static final String SELECT_AUTHORITIES_SQL =
            "SELECT AUTHORITIES.AUTH_ID, AUTHORITIES.AUTHORITY " +
            "FROM AUTHORITIES, USERS " +
            "WHERE AUTHORITIES.USER_ID = USERS.USER_ID AND USERS.USERNAME = ?";

    private static final String UPDATE_USER_SQL =
            "UPDATE USERS " +
            "SET PASSWORD = ?, ENABLED = ? " +
            "WHERE USERNAME = ?";

    private static final String DELETE_USER_SQL =
            "DELETE " +
            "FROM USERS " +
            "WHERE USERNAME = ?";

    private static final String DELETE_AUTHORITIES_SQL =
            "DELETE AUTHORITIES " +
            "FROM AUTHORITIES, USERS " +
            "WHERE AUTHORITIES.USER_ID = USERS.USER_ID AND USERNAME = ?";


    /** USERS table*/
    private static final String USERS_ID = "USER_ID";
    private static final String USERS_USERNAME = "USERNAME";
    private static final String USERS_PASSWORD = "PASSWORD";
    private static final String USERS_ENABLED = "ENABLED";
    private static final String USERS_FIRST_NAME = "FIRSTNAME";
    private static final String USERS_LAST_NAME = "LASTNAME";


    /** AUTHORITIES table*/
    private static final String AUTHORITIES_ID = "AUTH_ID";
    private static final String AUTHORITIES_AUTHORITY = "AUTHORITY";

    public JBDCProvider(final DataSource dataSource) {
        setJdbcTemplate(new JdbcTemplate(dataSource));
    }

    @Override
    public void createUser(final AuthUser user) {

        validateUserDetails(user);

        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                                connection.prepareStatement(CREATE_USER_SQL, new String[]{USERS_ID});
                        ps.setString(1, user.getUsername());
                        ps.setString(2, user.getPassword());
                        ps.setBoolean(3, user.isEnabled());
                        ps.setString(4, user.getFirstName());
                        ps.setString(5, user.getLastName());
                        return ps;
                    }
                },
                keyHolder);

        user.setId(keyHolder.getKey().longValue());

        insertUserAuthorities(user);
    }

    @Override
    public void updateUser(final AuthUser user) {

        validateUserDetails(user);

        getJdbcTemplate().update(UPDATE_USER_SQL, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, user.getPassword());
                ps.setBoolean(2, user.isEnabled());
                ps.setString(3, user.getUsername());
            }
        });

        deleteUserAuthorities(user.getUsername());
        insertUserAuthorities(user);
    }

    @Override
    public void deleteUser(final String username) {
        Assert.notNull(username);
        deleteUserAuthorities(username);
        getJdbcTemplate().update(DELETE_USER_SQL, username);
    }

    @Override
    public void changePassword(final String oldPassword,
                               final String newPassword) {

    }

    @Override
    public boolean userExists(final String username) {
        return true;
    }

    @Override
    public AuthUser loadUserByUsername(final String username) throws UsernameNotFoundException {

        Assert.notNull(username);

        List<UserAuthority> authorities = loadUserAuthorities(username);

        if (authorities.size() == 0) {
            throw new UsernameNotFoundException("User " + username + " has no GrantedAuthority");
        }

        List<AuthUser> users = loadUsersByUsername(username, authorities);

        if (users.size() == 0) {
            throw new UsernameNotFoundException("Username " + username + " not found");
        }

        return users.get(0);
    }

    private List<UserAuthority> loadUserAuthorities(final String username) {
        return getJdbcTemplate().query(SELECT_AUTHORITIES_SQL, new String[] {username},
                new RowMapper<UserAuthority>() {
                    public UserAuthority mapRow(ResultSet rs, int rowNum) throws SQLException {
                        String authority = rs.getString(AUTHORITIES_AUTHORITY);
                        Long id = rs.getLong(AUTHORITIES_ID);
                        return new UserAuthority(id, authority);
                    }
                });
    }


    private List<AuthUser> loadUsersByUsername(final String username,
                                               final List<UserAuthority> authorities) {
        return getJdbcTemplate().query(SELECT_USER_SQL, new String[] {username},
            new RowMapper<AuthUser>() {
                public AuthUser mapRow(ResultSet rs, int rowNum) throws SQLException {
                    String username = rs.getString(USERS_USERNAME);
                    String password = rs.getString(USERS_PASSWORD);
                    boolean enabled = rs.getBoolean(USERS_ENABLED);
                    String firstName = rs.getString(USERS_FIRST_NAME);
                    String lastName = rs.getString(USERS_LAST_NAME);
                    Long id = rs.getLong(USERS_ID);

                    AuthUser user = new AuthUser(firstName, lastName, username, password, enabled, authorities);
                    user.setId(id);
                    return user;
                }

        });
    }

    private void insertUserAuthorities(final AuthUser user) {
        for (GrantedAuthority auth : user.getAuthorities()) {
            insertUserAuthority(user.getUsername(), (UserAuthority) auth);
        }
    }

    private void insertUserAuthority(final String userName,
                                     final UserAuthority userAuthority) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(
                new PreparedStatementCreator() {
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps =
                                connection.prepareStatement(CREATE_AUTHORITY_SQL, new String[] {AUTHORITIES_ID});
                        ps.setString(1, userAuthority.getAuthority());
                        ps.setString(2, userName);
                        return ps;
                    }
                },
                keyHolder);
        userAuthority.setId(keyHolder.getKey().longValue());
    }

    private void deleteUserAuthorities(final String username) {
        Assert.notNull(username);
        getJdbcTemplate().update(DELETE_AUTHORITIES_SQL, username);
    }

    private void validateUserDetails(final AuthUser user) {
        Assert.hasText(user.getUsername(), "Username may not be empty or null");
        validateAuthorities(user.getAuthorities());
    }

    private void validateAuthorities(final Collection<? extends GrantedAuthority> authorities) {
        Assert.notNull(authorities, "Authorities list must not be null");

        for (GrantedAuthority authority : authorities) {
            Assert.notNull(authority, "Authorities list contains a null entry");
            Assert.hasText(authority.getAuthority(), "getAuthority() method must return a non-empty string");
        }
    }

}
